package daoImp;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;

import org.apache.log4j.Logger;

import util.DateUtil;

import dao.TweetKeywordsDAO;

public class TweetKeywordsDAOImp implements TweetKeywordsDAO{
	private static TweetKeywordsDAOImp uniqueInstance = new TweetKeywordsDAOImp();
	private ConnectionPool pool = ConnectionPool.getInstance();
	private Logger logger;
	
	public static TweetKeywordsDAO getInstance() {
		return uniqueInstance;
	}

	private TweetKeywordsDAOImp() {
		logger = Logger.getLogger(TweetKeywordsDAOImp.class);
	}

	private void saveKeywords(String trend, Map<String, Integer> keywords,
			Date date) {
		String sql = null;
		Statement stmt = null;
		Connection connection;
		try {
			connection = pool.getConnection();
		} catch (Exception e) {
			logger.error("connection error : ", e);
			return;
		}
		try {
			connection.setAutoCommit(false);
			stmt = connection.createStatement();
		} catch (SQLException e1) {
			logger.error("connection error : ", e1);
		}
		Set<String> words = keywords.keySet();
		Iterator<String> ite = words.iterator();
		String dateStr = DateUtil.getNewsDateStr(date);
		trend = trend.replaceAll("'", "\\\\'").replaceAll("\\)", "\\\\)").replaceAll("\\(", "\\\\(");
		while(ite.hasNext()) {
			String word = ite.next();
			int num = keywords.get(word);
			sql = "insert into tweetkeywords (trend , keyword , frequency, date)" + "value( '"
					+ trend + "', '" + word + "'," + num + ",'" + dateStr +"');";
			try {
				stmt.executeUpdate(sql);
			} catch (SQLException e)
			{
				logger.error("sql error : " + sql, e);
			}
		}
		try {
			connection.commit();
		} catch (SQLException e) {
			logger.error("sql error : " + sql, e);
		}finally{
			pool.returnConnection(connection);
		}
	}

	public Map<String, Integer> getFrequencyByTrend(String trend, Date date) {
		String sql = null;
		Map<String, Integer> frequency = new HashMap<String, Integer>();
		try {
			Connection connection;
			try {
				connection = pool.getConnection();
			} catch (Exception e) {
				logger.error("connection error : ", e);
				return frequency;
			}
			Statement stmt = connection.createStatement();
			String dateStr = DateUtil.getNewsDateStr(date);
			trend = trend.replaceAll("'", "\\\\'").replaceAll("\\)", "\\\\)").replaceAll("\\(", "\\\\(");
			sql = "select * from tweetkeywords where trend = '" + trend + "' and date = '"+ dateStr +"';";
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String word = rs.getString(3);
				int num = rs.getInt(4);
				frequency.put(word,num);
			}
			pool.returnConnection(connection);
		} catch (SQLException e) {
			logger.error("sql error : " + sql, e);
		}
		return frequency;
	}

	public void updateKeywords(Map<String, Integer> newFrequency, String trend, Date date) {
		deleteKeywordsByTrend(trend, date);
		saveKeywords(trend, newFrequency, date);
	}
	
	private void deleteKeywordsByTrend(String trend, Date date){
		String sql = null;
		try {
			Connection connection;
			try {
				connection = pool.getConnection();
			} catch (Exception e) {
				logger.error("connection error : ", e);
				return;
			}
			Statement stmt = connection.createStatement();
			String dateStr = DateUtil.getNewsDateStr(date);
			trend = trend.replaceAll("'", "\\\\'").replaceAll("\\)", "\\\\)").replaceAll("\\(", "\\\\(");
			sql = "delete  from tweetkeywords where trend = '" + trend + "' and date = '"+ dateStr +"';";
			stmt.execute(sql);
			pool.returnConnection(connection);
		} catch (SQLException e) {
			logger.error("sql error : " + sql, e);
		}
	}
}
